function calculateFuelConsumptionData(fillupdata) {
	var fillups = [];
	var partials = {};
	
	for(var i=0; i<fillupdata.length;i++) {
		var fillup_id = fillupdata[i][0];
		var vehicle_odometer = fillupdata[i][1];
		var fillup_units = fillupdata[i][2];
		var belongs_to_fillup = fillupdata[i][3];
		
		// If we hit a partial and it doesn't point anywhere, this must be the last fillup
		if(belongs_to_fillup == 0) {
			break;
		}
		
		if(belongs_to_fillup != null) {
			if(!(belongs_to_fillup in partials)) {
				partials[belongs_to_fillup] = 0.0;
			}
				
			partials[belongs_to_fillup] += fillup_units;
		} else {
			var partial_fillup_units = 0.0;
			if(fillup_id in partials) {
				partial_fillup_units = partials[fillup_id];
			}
			
			fillups.push([vehicle_odometer, partial_fillup_units + fillup_units]);
		}
	}
	
	var previous_odometer = null, previous_fillup_units = null, bestcons = null, worstcons = null;
	var consumptions = [];
	
	for(var i = 0; i < fillups.length; i++) {
		
		if(previous_odometer != null && previous_fillup_units != null) {
			var current_odometer = fillups[i][0];
			var current_fillup_units = fillups[i][1];
			
			var fuelcons = current_fillup_units / (current_odometer - previous_odometer);
			// Best fuel consumption
			if(bestcons != null) {
				if(bestcons > fuelcons) {
					bestcons = fuelcons;
				}
			} else {
				bestcons = fuelcons;
			}
			
			// Worst fuel consumption
			if(worstcons != null) {
				if(worstcons < fuelcons) {
					worstcons = fuelcons;
				}
			} else {
				worstcons = fuelcons;
			}
			
			// Add to consumptions in order to calculate average later
			consumptions.push(fuelcons);
		}
		
		previous_odometer = fillups[i][0];
		previous_fillup_units = fillups[i][1];
	}
	
	//var consumptions_sum = consumptions.reduce(function(a, b) { return a + b });
	var consumptions_sum = 0;
	for(var i = 0, l = consumptions.length; i < l; i++) {
		consumptions_sum += consumptions[i];
	}
	var avgcons = consumptions_sum / consumptions.length;
	
	return {
		'average': ((consumptions_sum / consumptions.length)*100).toFixed(2),
		'last': (consumptions[consumptions.length-1]*100).toFixed(2),
		'best': (bestcons*100).toFixed(2),
		'worst': (worstcons*100).toFixed(2)
	};
}

exports.findAll = function() {
	var Database = require('/database/Database');
	var db = new Database();
	var vehiclesResult = []; 
	
	// Before everything, let's grab the vehicles with absolutely no fillups yet. They will be on top of the list.
	var noFillupsResultSet = db.execute('select v.id, v.make, v.model, vp.photo, vp.iswide ' + 
		'from vehicle v ' +
		'left outer join vehicle_photo vp on (v.ID=vp.vehicleID) ' +
		'left outer join vehicle_fillup vf on (v.id=vf.vehicleid) ' +
		'where vf.id is null');
		
	while(noFillupsResultSet.isValidRow()) {
		vehiclesResult.push({
			id: noFillupsResultSet.fieldByName('id'),
			make: noFillupsResultSet.fieldByName('make'),
			model: noFillupsResultSet.fieldByName('model'),
			lastFillup: null,
			lastFillupUnits: null,
			distancePerFillupUnit: null,
			fillupUnitsPerDistance: null,
			distancePerFillupUnitBest: null,
			fillupUnitsPerDistanceBest: null,
			photo: noFillupsResultSet.fieldByName('photo'),
			isPhotoWide: noFillupsResultSet.fieldByName('iswide')
		});
			
		noFillupsResultSet.next();
	}

	// 2. For each vehicle, put in object based on vehicleID
	var vehicles = {};
	var initialResultSet = db.execute('select vehicleid, id, vehicle_odometer, fillup_units, ' + 
		'belongs_to_fillup from vehicle_fillup');
	
	while(initialResultSet.isValidRow()) {
		var vehicleId = initialResultSet.fieldByName('vehicleid');
		var fillup_id = initialResultSet.fieldByName('id');
		var vehicle_odometer = initialResultSet.fieldByName('vehicle_odometer');
		var fillup_units = initialResultSet.fieldByName('fillup_units');
		var belongs_to_fillup = initialResultSet.fieldByName('belongs_to_fillup');
		
		if(!(vehicleId in vehicles)) {
			vehicles[vehicleId] = [];
		}
		
		vehicles[vehicleId].push([fillup_id, vehicle_odometer, fillup_units, belongs_to_fillup]);
		
		initialResultSet.next();
	}
	
	
	
	for(var vehicleId in vehicles) {
		// Take vehicleId and get actual fuel consumption data
		var fuelConsumptionData = calculateFuelConsumptionData(vehicles[vehicleId]);
		
		// Get other vehicle information, photo, etc. and add to vehicle result set
		var additionalResultSet = db.execute(
			'select v.make, v.model, ' +
			'(select max(vf.fillup_date) from vehicle_fillup vf where vf.vehicleID=v.id) as lastfillup, ' +
			'(select fillup_units from vehicle_fillup where vehicleID=v.id ' + 
			'                order by fillup_date desc limit 1) as lastfillupunits, ' +
			'vp.photo, vp.iswide ' +
			'from vehicle v ' +
			'left outer join vehicle_photo vp on (v.ID=vp.vehicleID) ' +
			'where v.ID=?',
			vehicleId
		);
		
		if(additionalResultSet.isValidRow()) {
			
			vehiclesResult.push({
				id: vehicleId,
				make: additionalResultSet.fieldByName('make'),
				model: additionalResultSet.fieldByName('model'),
				lastFillup: additionalResultSet.fieldByName('lastfillup'),
				lastFillupUnits: additionalResultSet.fieldByName('lastfillupunits'),
				distancePerFillupUnit: (100 / fuelConsumptionData['average']).toFixed(2),
				fillupUnitsPerDistance: fuelConsumptionData['average'],
				distancePerFillupUnitBest: (100 / fuelConsumptionData['best']).toFixed(2),
				fillupUnitsPerDistanceBest: fuelConsumptionData['best'],
				photo: additionalResultSet.fieldByName('photo'),
				isPhotoWide: additionalResultSet.fieldByName('iswide')
			});
		}
	}
		
	db.close();
	
	return vehiclesResult;
}

exports.findById = function(vehicleId) {
	var vehicle = null;
	
	var Database = require('/database/Database');
	var db = new Database();
	
	var vehicleResultSet = db.execute(
		'select v.make, v.model, (select name from fueltype where ID=v.fueltypeID) as fueltype, ' + 
		'v.comments, vp.photo, vp.iswide from vehicle v ' + 
		'left outer join vehicle_photo vp on (vp.vehicleID=v.ID) where v.ID=?', vehicleId);
	
	if(vehicleResultSet.isValidRow())
	{
		var vehicleMake = vehicleResultSet.fieldByName('make');
		var vehicleModel = vehicleResultSet.fieldByName('model');
		var fuelType =  vehicleResultSet.fieldByName('fueltype');
		var comments =  vehicleResultSet.fieldByName('comments');
		if(comments == 'None') {
			comments = '';
		}
		var photo =  vehicleResultSet.fieldByName('photo');
		var isPhotoWide =  vehicleResultSet.fieldByName('iswide');
		
		vehicle = {
			vehicleMake: vehicleMake,
			vehicleModel: vehicleModel,
			fuelType: fuelType,
			comments: comments,
			photo: photo,
			isPhotoWide: isPhotoWide
		};
	}
	
	vehicleResultSet.close();
	db.close();
	
	return vehicle;
}

exports.save = function(make, model, fuelType, comments, photo, isWidePhoto) {
	try {
		var Database = require('database/Database');
		var db = new Database();
	
		// Insert vehicle
		db.execute(
			'insert into vehicle (ID, make, model, fueltypeID, comments) ' +
			'values (NULL, ?, ?, (select ID from fueltype where name=?), ?)', 
			make, model, fuelType, comments);
		
		// Get the vehicle Id we just added
		var vehicleId = db.execute('select last_insert_rowid()').field(0);
		
		// Insert vehicle photo
		db.execute(
			'insert into vehicle_photo (ID, vehicleID, photo, iswide) values (NULL, ?, ?, ?)',
			vehicleId, photo, isWidePhoto
		);
		
		db.close();
		
	} catch(e) {
		Ti.UI.createAlertDialog({
			message: 'Could not add vehicle',
			ok: 'Okay',
			title: 'Oops.'
		})
	}
}


exports.update = function(vehicleId, make, model, fuelType, comments, photo, isWidePhoto) {
	try {
		var Database = require('database/Database');
		var db = new Database();
	
		// Insert vehicle
		db.execute(
			'update vehicle set make=?, model=?, fueltypeID=(select ID from fueltype where name=?), ' + 
			'comments=? where ID=?', 
			make, model, fuelType, comments, vehicleId);
		
		// Insert vehicle photo
		if(photo != null) {
			// Delete existing photo
			db.execute(
				'delete from vehicle_photo where vehicleID=?',
				vehicleId
			);
			
			// Insert vehicle photo
			db.execute(
				'insert into vehicle_photo (ID, vehicleID, photo, iswide) values (NULL, ?, ?, ?)',
				vehicleId, photo, isWidePhoto
			);
		}
		db.close();
		
	} catch(e) {
		Ti.UI.createAlertDialog({
			message: 'Could not edit vehicle',
			ok: 'Okay',
			title: 'Oops.'
		})
	}
}

exports.remove = function(vehicleId) {
	// Delete vehicle
	var Database = require('/database/Database');
	var db = new Database();
	
	// First we delete all vehicle_photo
	db.execute('delete from vehicle_photo where vehicleID=?', vehicleId);
	
	// Then delete vehicle_fillup entries
	db.execute('delete from vehicle_fillup where vehicleID=?', vehicleId);
	
	// Then delete actual vehicle
	db.execute('delete from vehicle where ID=?', vehicleId);
	
	db.close();
}

exports.findVehicleCostsPerVehicleId = function(vehicleId) {
	var Database = require('/database/Database');
	var db = new Database();
	
    var fillupsResultSet = db.execute(
    	'select ' + 
    	'fillup_units*fillup_price_per_unit/(max(vehicle_odometer)-min(vehicle_odometer)) as cost_per_distance,' + 
    	'avg(fillup_price_per_unit) as cost_per_unit, ' + 
    	'sum(fillup_units*fillup_price_per_unit) as total_costs ' +
    	'from vehicle_fillup where vehicleID=?',
    	vehicleId
    );
    
	if(fillupsResultSet.isValidRow()) {
		var cost_per_distance = fillupsResultSet.fieldByName('cost_per_distance');
		var cost_per_unit = fillupsResultSet.fieldByName('cost_per_unit');
		var total_costs = fillupsResultSet.fieldByName('total_costs');
		
		return {
			costPerDistance: cost_per_distance,
			costPerUnit: cost_per_unit,
			totalCosts: total_costs
		};
	}
	
	db.close();
}
